package org.zoomdev.zoom.dao.ddl.utils;

import org.apache.commons.lang3.StringUtils;
import org.zoomdev.zoom.dao.ddl.SqlType;
import org.zoomdev.zoom.dao.driver.MysqlDriver;
import org.zoomdev.zoom.dao.meta.ColumnMeta;
import org.zoomdev.zoom.dao.meta.IndexMeta;
import org.zoomdev.zoom.dao.meta.TableKeyMeta;
import org.zoomdev.zoom.dao.meta.TableMeta;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public final class MysqlMetaUtils {


    public static TableMeta getTableMeta(Connection conn, String tableName) throws SQLException {
        try {
            TableMeta tableMeta = new TableMeta();
            tableMeta.setName(tableName);
            // 获取表引擎
            fillTableMeta(conn, tableMeta);

            return tableMeta;
        } catch (SQLException e) {
            throw new SQLException("处理" + tableName + "发生异常", e);
        }
    }

    /**
     * 获取表存储引擎
     */
    public static void fillTableMeta(Connection conn, TableMeta tableMeta) throws SQLException {
        String tableName = tableMeta.getName();
        fillTableMetaProperties(conn, tableMeta);

        // 获取列信息（使用information_schema.COLUMNS表）
        tableMeta.setColumns(getColumnsFromInfoSchema(conn, tableName));

        // 获取主键信息
        tableMeta.setPrimaryKeys(getPrimaryKeysFromInfoSchema(conn, tableName));

        // 获取索引信息
        tableMeta.setIndexes(getIndexesFromInfoSchema(conn, tableName));

    }

    public static void fillTableMeta(Connection conn, TableKeyMeta tableMeta) throws SQLException {
        String tableName = tableMeta.getName();

        tableMeta.setColumns(MetaUtils.getColumnTypes(conn, tableName));

        // 获取主键信息
        tableMeta.setPrimaryKeys(getPrimaryKeysFromInfoSchema(conn, tableName));


    }

    private static void fillTableMetaProperties(Connection conn, TableMeta tableMeta) throws SQLException {
        String dbName = conn.getCatalog();
        String tableName = tableMeta.getName();
        String sql = "SELECT ENGINE,TABLE_COMMENT FROM information_schema.TABLES " +
                "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dbName);
            stmt.setString(2, tableName);

            try (ResultSet rs = stmt.executeQuery()) {
                String engine;
                String comment;
                if (rs.next()) {
                    engine = rs.getString("ENGINE");
                    comment = rs.getString("TABLE_COMMENT");
                } else {
                    engine = "InnoDB"; // 默认引擎
                    comment = "";
                }
                tableMeta.setComment(comment);
                tableMeta.setEngine(engine);
            }
        }
    }

    /**
     * 从information_schema.COLUMNS获取列信息
     */
    private static ColumnMeta[] getColumnsFromInfoSchema(
            Connection conn, String tableName) throws SQLException {
        ArrayList<ColumnMeta> columns = new ArrayList<>();
        String dbName = conn.getCatalog();

        String sql = "SELECT " +
                "  COLUMN_NAME, " +
                "  COLUMN_TYPE, " +
                "  CHARACTER_MAXIMUM_LENGTH, " + // 字符类型的最大长度
                "  NUMERIC_PRECISION, " +        // 数值类型的精度
                "  NUMERIC_SCALE, " +            // 数值类型的小数位数
                "  IS_NULLABLE, " +
                "  COLUMN_DEFAULT, " +
                "  COLUMN_COMMENT, " +
                //"  COLUMN_KEY, " +
                "  EXTRA " +
                "FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dbName);
            stmt.setString(2, tableName);


            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    ColumnMeta meta = new ColumnMeta();
                    meta.setName(rs.getString("COLUMN_NAME"));
                    String columnType = rs.getString("COLUMN_TYPE");
                    meta.setType(parseDateType(columnType));
                    // 设置长度信息
                    if (meta.getType().hasLength()) {
                        long charLength = rs.getLong("CHARACTER_MAXIMUM_LENGTH");
                        if (!rs.wasNull()) {
                            meta.setSize(charLength);
                        }
                    } else if (meta.getType().hasScale()) {
                        // 数值精度信息
                        int numericPrecision = rs.getInt("NUMERIC_PRECISION");
                        if (!rs.wasNull()) {
                            meta.setSize(numericPrecision);
                        }
                        // 数值小数位数
                        int numericScale = rs.getInt("NUMERIC_SCALE");
                        if (!rs.wasNull()) {
                            meta.setScale(numericScale);
                        }
                    } else if (meta.getType() == SqlType.SET || meta.getType() == SqlType.ENUM) {
                        //解析
                        String str = columnType.substring(columnType.indexOf("(") + 1, columnType.length() - 1);
                        String[] parts = str.split(",");
                        String[] values = new String[parts.length];
                        for (int i = 0; i < parts.length; ++i) {
                            values[i] = parseValue(parts[i]);
                        }
                        meta.setValues(values);
                    }


                    // 键类型
                    // String columnKey = rs.getString("COLUMN_KEY");
                    //meta.setKeyType(parseKeyType(columnKey));

                    String extra = rs.getString("EXTRA");
                    // 处理额外的属性
                    meta.setAutoIncrement(StringUtils.contains(extra, "auto_increment"));

                    meta.setNotNull(!"YES".equalsIgnoreCase(rs.getString("IS_NULLABLE")));
                    meta.setDefaultValue(parseDefaultValue(meta, rs));
                    meta.setComment(rs.getString("COLUMN_COMMENT"));

                    columns.add(meta);
                }
            }
        }

        return columns.toArray(new ColumnMeta[0]);
    }

    /**
     * 从information_schema获取主键信息
     */
    private static String[] getPrimaryKeysFromInfoSchema(
            Connection conn, String tableName) throws SQLException {

        String dbName = conn.getCatalog();
        List<String> keys = new ArrayList<>();
        String sql = "SELECT " +
                "  COLUMN_NAME " +
                "FROM information_schema.KEY_COLUMN_USAGE " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "AND CONSTRAINT_NAME = ? " +
                "ORDER BY ORDINAL_POSITION";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dbName);
            stmt.setString(2, tableName);
            stmt.setString(3, "PRIMARY");

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    keys.add(rs.getString("COLUMN_NAME"));
                }
            }
        }

        return keys.toArray(new String[0]);
    }

    /**
     * 从information_schema获取索引信息
     */
    private static IndexMeta[] getIndexesFromInfoSchema(
            Connection conn, String tableName) throws SQLException {

        Map<String, List<Map<String, Object>>> indexes = new LinkedHashMap<>();
        String dbName = conn.getCatalog();

        String sql = "SELECT " +
                "  INDEX_NAME, " +
                "  COLUMN_NAME, " +
                "  SEQ_IN_INDEX, " +
                "  NON_UNIQUE " +
                "FROM information_schema.STATISTICS " +
                "WHERE TABLE_SCHEMA = ? " +
                "AND TABLE_NAME = ? " +
                "AND INDEX_NAME != ? " +
                "ORDER BY INDEX_NAME, SEQ_IN_INDEX";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, dbName);
            stmt.setString(2, tableName);
            stmt.setString(3, "PRIMARY");

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    String indexName = rs.getString("INDEX_NAME");
                    boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                    String columnName = rs.getString("COLUMN_NAME");

                    Map<String, Object> map = new HashMap<>();
                    map.put("name", indexName);
                    map.put("unique", !nonUnique);
                    map.put("column", columnName);

                    indexes.computeIfAbsent(indexName, k -> new ArrayList<>())
                            .add(map);
                }
            }
        }

        List<IndexMeta> indexMetas = new ArrayList<>();
        indexes.forEach((k, v) -> {
            IndexMeta meta = new IndexMeta();
            meta.setName(k);
            meta.setColumns(v.stream().map(m -> (String) m.get("column")).toArray(String[]::new));
            meta.setUnique((Boolean) v.get(0).get("unique"));
            indexMetas.add(meta);
        });

        return indexMetas.toArray(new IndexMeta[0]);
    }

    private static SqlType parseDateType(String dataType) {
        // 统一转换为大写便于处理
        String normalized = dataType.trim().toUpperCase();

        // 提取基本类型（去掉参数部分）
        String baseType = extractBaseType(normalized);

        // 检查无符号标记
        boolean unsigned = normalized.contains(" UNSIGNED");

        // 根据基本类型匹配 SqlType
        switch (baseType) {
            // 整数类型
            case "INT":
            case "INTEGER":
                return unsigned ? SqlType.INT_UNSIGNED : SqlType.INT;

            case "TINYINT": {
                String lenPart = extraLengthType(dataType);
                if ("1".equals(lenPart)) {
                    return SqlType.BOOLEAN;
                }
                return unsigned ? SqlType.TINYINT_UNSIGNED : SqlType.TINYINT;
            }


            case "SMALLINT":
                return unsigned ? SqlType.SMALLINT_UNSIGNED : SqlType.SMALLINT;

            case "MEDIUMINT":
                return unsigned ? SqlType.MEDIUMINT_UNSIGNED : SqlType.MEDIUMINT; // MySQL特有的中等大小整数

            case "BIGINT":
                return unsigned ? SqlType.BIGINT_UNSIGNED : SqlType.BIGINT;

            // 浮点类型
            case "FLOAT":
                return SqlType.FLOAT;

            case "DOUBLE":
                return SqlType.DOUBLE;

            case "DECIMAL":
            case "NUMERIC":
                return SqlType.DECIMAL;

            // 日期时间类型
            case "DATE":
                return SqlType.DATE;

            case "TIME":
                return SqlType.TIME;

            case "DATETIME":
                return SqlType.DATETIME;

            case "TIMESTAMP":
                return SqlType.TIMESTAMP;

            case "YEAR": // MySQL特有类型，处理为SMALLINT
                return SqlType.YEAR;

            // 字符串类型
            case "CHAR":
            case "NCHAR": // SQL Server 的 Unicode CHAR
                return SqlType.CHAR;

            case "VARCHAR":
            case "NVARCHAR": // SQL Server 的 Unicode VARCHAR
            case "VARCHAR2": // Oracle 的 VARCHAR
                return SqlType.VARCHAR;

            case "JSON":
                return SqlType.JSON;

            // 大文本类型
            case "TEXT":
                return SqlType.TEXT;

            case "TINYTEXT":
                return SqlType.TINYTEXT;

            case "MEDIUMTEXT":
                return SqlType.MEDIUMTEXT;

            case "LONGTEXT":
                return SqlType.LONGTEXT;

            // 大对象类型
            case "BLOB":
                return SqlType.BLOB;

            case "TINYBLOB":
                return SqlType.TINYBLOB;

            case "MEDIUMBLOB":
                return SqlType.MEDIUMBLOB;

            case "LONGBLOB":
                return SqlType.LONGBLOB;

            case "CLOB": // Oracle/SQL Server 的字符大对象
            case "NCLOB": // Oracle 的 Unicode CLOB
                return SqlType.CLOB;

            // 其他特殊类型
            case "BIT":
            case "BOOL":
            case "BOOLEAN":
                return SqlType.BOOLEAN;

            case "ENUM":
                return SqlType.ENUM;
            case "SET":
                return SqlType.SET;

            case "BINARY": // 固定长度二进制
                return SqlType.BINARY;

            case "VARBINARY": // 可变长度二进制
                return SqlType.VARBINARY;

            case "LONG": // Oracle 的长字符串类型
                return SqlType.TEXT;

            case "RAW": // Oracle 的原始二进制
            case "LONG RAW": // Oracle 的长原始二进制
                return SqlType.BLOB;

            // 未知类型处理
            default:
                // 尝试从复杂类型中识别
                if (normalized.startsWith("GEOMETRY") ||
                        normalized.startsWith("POINT") ||
                        normalized.startsWith("LINESTRING") ||
                        normalized.startsWith("POLYGON") ||
                        normalized.startsWith("MULTIPOINT") ||
                        normalized.startsWith("MULTILINESTRING") ||
                        normalized.startsWith("MULTIPOLYGON") ||
                        normalized.startsWith("GEOMETRYCOLLECTION")) {
                    return SqlType.BLOB; // 空间数据类型视为二进制
                }

                // 无法识别的类型默认为 VARCHAR
                return SqlType.VARCHAR;
        }
    }

    private static String parseValue(String part) {
        return part.substring(1, part.length() - 1);
    }

    private static String parseDefaultValue(ColumnMeta meta, ResultSet rs) throws SQLException {
        String def = rs.getString("COLUMN_DEFAULT");
        if (def == null) {
            return null;
        }
        String extra = rs.getString("EXTRA");
        // mysql 函数检查
        if (MysqlDriver.CURRENT_TIMESTAMP.equals(def)) {
            if (extra.contains(MysqlDriver.CURRENT_TIMESTAMP)) {
                return MysqlDriver.CURRENT_TIMESTAMP_ON_UPDATE;
            }
            return MysqlDriver.CURRENT_TIMESTAMP;
        }


        return null;
    }

//    private static KeyType parseKeyType(String columnKey) {
//        if ("PRI".equalsIgnoreCase(columnKey)) return KeyType.PRIMARY;
//        if ("UNI".equalsIgnoreCase(columnKey)) return KeyType.UNIQUE;
//        if ("MUL".equalsIgnoreCase(columnKey)) return KeyType.INDEX;
//        return null;
//    }

    /**
     * 提取数据库类型的基本部分（移除长度、精度等信息）
     */
    private static String extractBaseType(String dataType) {
        // 处理带有参数的复杂类型
        if (dataType.contains("(")) {
            return dataType.substring(0, dataType.indexOf('(')).trim();
        }

        // 处理带空格的类型声明（如 "UNSIGNED INT"）
        if (dataType.contains(" ")) {
            // 如果是无符号声明，移除这部分
            if (dataType.contains(" UNSIGNED")) {
                return dataType.replace(" UNSIGNED", "").trim();
            }
            // 返回第一个单词
            return dataType.substring(0, dataType.indexOf(' '));
        }

        return dataType;
    }

    private static String extraLengthType(String dateType) {
        int index = dateType.indexOf('(');
        if (index > 0) {
            return dateType.substring(index + 1, dateType.indexOf(')', index + 1));
        }
        return null;
    }
}
